<?php


namespace App\Models;


use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class WarrantyStat extends Model
{
    protected $table = 'xpel_warranty_stat';

    public function warrantyTotal(string $start_date, string $end_date)
    {
        $sql = 'SELECT date_format(approved_date, "%Y-%m-%d") AS date, COUNT(1) AS total FROM warranty WHERE approved=1 AND deleted_at IS NULL AND approved_date>="' . $start_date . '" AND approved_date<="' . $end_date . '" GROUP BY date';
        $warrantyTotal = DB::select($sql);

        return $warrantyTotal ?? [];
    }

    public function warrantyNum(string $start_date, string $end_date, int $type)
    {
        /*$select = $type == 1 ? 'roll_1' : 'roll_2';

        $total = DB::table('xpel_warranty_stat')
            ->where('create_date', '>=', $start_date)
            ->where('create_date', '<', $end_date)
            ->groupBy('create_date')
            ->select('create_date as date', DB::Raw('SUM(' . $select . ') as total'))
            ->get();*/

        $total = DB::table('warranty', 'w')
            ->whereNull('w.deleted_at')
            ->leftJoin('users as u', 'w.user_id', '=', 'u.id')
            ->leftJoin('warranty_to_vehicle_coverage as wvc', 'w.id', '=', 'wvc.warranty_id', function ($query) {
                $query->whereNull('wvc.deleted_at');
            })
            ->leftJoin('new_precut as p', 'w.precut_id', '=', 'p.id')
            ->leftJoin('film_type_master as fm', 'fm.id', 'wvc.film_type_id')
            ->where('approved_date', '>=', $start_date)
            ->where('approved', 1)
            ->where('fm.product_type_id', $type)
            ->select(DB::raw('date_format(approved_date, "%Y-%m-%d") AS date'), DB::raw('count(distinct(w.id)) as total'))
            ->groupBy('date')
            ->get();

        return $total;
    }

    public function warrantyRoll(string $start_date, string $end_date, int $film_type_id)
    {
        $sql = 'SELECT COUNT(w.id) AS total, date_format(approved_date, "%Y-%m-%d") AS date FROM warranty_to_vehicle_coverage wc LEFT JOIN warranty w ON wc.warranty_id=w.id WHERE approved=1 AND approved_date>="' . $start_date . '" AND film_type_id="' . $film_type_id . '" AND w.deleted_at IS NULL AND wc.deleted_at IS NULL GROUP BY date';
        $film = DB::table('film_type_master')->where('id', $film_type_id)->first();
        $data = DB::select($sql);

        return [
            'list'  => $data,
            'abbr'  => $film->english_value,
        ];
    }
}